package MypageController.MyMain;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import Connect.connector;
import MypageController.MyComment.MyCommentBean;
import MypageController.MyKategore.MyKategoreBean;
import MypageController.MyWrite.MyWriteBean;

public class MyMainDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	boolean comparison = false;
	
	public int existence (int idnumber, String id){
		conn = connector.getConnection();
		int mypageid = -1;
		try{
			
			String sql = "select `ID` from BLOGS where Members_Id = ? || Blog_Share = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, idnumber);
			pstmt.setString(2, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				mypageid = rs.getInt("ID");
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return mypageid;
	}
	
	public int level (String id){
		conn = connector.getConnection();
		int level = 0;
		try{
		
			
			String sql = "select * from MEMBERS where Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				level = rs.getInt("Mem_Level");

			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}

	public int idGet (String id){
		conn = connector.getConnection();
		int idnum = 0;
		try{
			
			String sql = "select `ID` from MEMBERS where Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				idnum = rs.getInt("ID");
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return idnum;
	}
	
	public boolean distinction (String id){
		conn = connector.getConnection();
		try{
			String sql = "select `ID` from MEMBERS where Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				comparison = true;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return comparison;
	}
	
	public int write (MyMainBean mymainbean){
		conn = connector.getConnection();
		int mypageId = -1;
		try{
			String sql = "INSERT INTO BLOGS (`ID`, `Members_Id`, `Blog_Share`, `Blog_Intro`, `Blog_Date`, `Blog_Title`) VALUES (NULL, ?, ?, ?, Sysdate(), ?)";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mymainbean.getMembers_Id());
			pstmt.setString(2, mymainbean.getBlog_Share());
			pstmt.setString(3, mymainbean.getBlog_Intro());
			pstmt.setString(4, mymainbean.getBlog_Tilte());

			pstmt.executeUpdate();
			
/*****************************************************************************************************/
			
			sql = "select `ID` from BLOGS where Members_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, mymainbean.getMembers_Id());
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				mypageId = rs.getInt("ID");
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return mypageId;
	}
	
	
	public int memberMypageIdSet (String id, int Mypagenumber){
		int idnum = 0;
		conn = connector.getConnection();
		try{
			
			String sql = "UPDATE MEMBERS SET Blogs_Id = ? WHERE Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, Mypagenumber);
			pstmt.setString(2, id);
			
			pstmt.executeUpdate();

		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return idnum;
	}
	
	public void kategorie (int Mypagenumber, String menu[]){
		conn = connector.getConnection();
		try{
			
			for(int i = 0; i < menu.length; i++){
				String sql = "INSERT INTO KATEGORIES (`Blogs_Id`, `Kate_Title`) VALUES ( ?, ?) ;";
			
				pstmt = conn.prepareStatement( sql );
			
				pstmt.setInt(1, Mypagenumber);
				pstmt.setString(2, menu[i]);
			
				pstmt.executeUpdate();
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}
	
	public int recognition(String id){
		conn = connector.getConnection();
		int mypageId = -1;
		try{

			
			String sql = "select `Blogs_Id` from MEMBERS where Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				mypageId = rs.getInt("Blogs_Id");
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return mypageId;
	}
	
	public String youNickGet(int id){
		conn = connector.getConnection();
		String younick = null;
		try{

			
			String sql = "select * from BLOGS where Members_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				younick = rs.getString("Blog_Share");
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return younick;
	}
	
	public String youNickGet2(String id){
		conn = connector.getConnection();
		String younick = "no";
		int younicknumber = -1;
		try{
			
			String sql = "select * from BLOGS where Blog_Share = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				younicknumber = rs.getInt("Members_Id");
			}
			if(younicknumber != -1){
				
				sql = "select * from MEMBERS where ID = ?";
				
				pstmt = conn.prepareStatement( sql );
				
				pstmt.setInt(1, younicknumber);
				
				rs = pstmt.executeQuery();
				
				if( rs.next()){
					younick = rs.getString("Mem_Nick");
				}
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return younick;
	}
	
	
	public Integer[] entriesIdGet(int mypageId){
		conn = connector.getConnection();
		ArrayList<Integer> list = new ArrayList<Integer>();
		try{
			
			String sql = "select ID from ENTRIES where Blogs_Id = ? ORDER BY `Ent_DateString` DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageId);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
					
				list.add(Integer.parseInt(rs.getString("ID")));
					
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new Integer[0]);
	}
	
	
	public MyWriteBean[] entriesGet(int mypageid, int number, int size){
		conn = connector.getConnection();
		ArrayList<MyWriteBean> result = new ArrayList<MyWriteBean>();
		int startnum;	
		try{

			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ENTRIES where Blogs_Id = ? ORDER BY Ent_DateString DESC) a where a.Ent_DateString limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setInt(2, startnum);
			pstmt.setInt(3, size);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				MyWriteBean mwbean = new MyWriteBean();
				mwbean.setID(rs.getInt("ID"));
				mwbean.setEnt_Title(rs.getString("Ent_Title"));
				mwbean.setEnt_Contents(rs.getString("Ent_Contents"));
				mwbean.setEnt_Nick(rs.getString("Ent_Nick"));
				mwbean.setEnt_Kategorie(rs.getString("Ent_Kategorie"));
				mwbean.setEnt_DateString(sdf.format(rs.getTimestamp("Ent_DateString")));
				mwbean.setEnt_Date(rs.getDate("Ent_Date"));
				mwbean.setEnt_ComCount(rs.getInt("Ent_ComCount"));
				mwbean.setEnt_Number(rs.getInt("Ent_Number"));
				result.add(mwbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyWriteBean[0]);
	}
	
	public void entriesNumberSet(int entriesid, int number){
		conn = connector.getConnection();
		try{
	
			String sql = "UPDATE ENTRIES SET `Ent_Number`=? WHERE `ID`=?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, number);
			pstmt.setInt(2, entriesid);
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public int entriesNumberGet (int entriesid){
		conn = connector.getConnection();
		int number = -1;
		try{
			
			String sql = "select Ent_Number from ENTRIES where ID = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, entriesid);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
				
				number = rs.getInt("Ent_Number");

			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return number;
	}
	
	public void commentCountSet(int entriesid){
		conn = connector.getConnection();
		int commentcount = 0;
		try{

			String sql = "select count(*) from COMMENTBLOGS where Entries_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, entriesid);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				commentcount = rs.getInt("count(*)");		
			}
			
/*****************************************************************************************************/
			
			sql = "UPDATE ENTRIES SET `Ent_ComCount`=? WHERE `ID`=?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, commentcount);
			pstmt.setInt(2, entriesid);
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public MyKategoreBean[] kategoriesGet(int mypageId){
		conn = connector.getConnection();
		ArrayList<MyKategoreBean> list = new ArrayList<MyKategoreBean>();
		try{
			
			String sql = "select * from KATEGORIES where Blogs_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageId);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
				MyKategoreBean katebean = new MyKategoreBean();
				katebean.setKeta_Title(rs.getString("Kate_Title"));
				list.add(katebean);
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new MyKategoreBean[0]);
	}
	
	public int kategorisCountGet(int mypageid, String katetitle){
		conn = connector.getConnection();
		int count = 0;
		try{
			String sql = "select count(*) from ENTRIES where Blogs_Id = ? && Ent_Kategorie = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, mypageid);
			pstmt.setString(2, katetitle);
			rs = pstmt.executeQuery();
			if(rs.next()){
				count = rs.getInt("count(*)");
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
	}
	
	public String[] kategoriesDateGet(int mypageId){
		conn = connector.getConnection();
		ArrayList<String> list = new ArrayList<String>();
		try{
			
			String sql = "select distinct Ent_Date from ENTRIES where Blogs_Id = ? ORDER BY `Ent_Date` DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageId);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
					
				list.add(rs.getString("Ent_Date"));
					
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new String[0]);
	}
	
	public MyCommentBean[] latestComment(int mypageId){
		conn = connector.getConnection();
		ArrayList<MyCommentBean> result = new ArrayList<MyCommentBean>();
		
		try{
			
			String sql = "select * from (select * from COMMENTBLOGS where Blogs_Id = ? ORDER BY Cblog_Date DESC) a where a.Cblog_Date limit 0, 5";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageId);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
			
			while( rs.next()){
				MyCommentBean mcbean = new MyCommentBean();
				mcbean.setID(rs.getInt("ID"));
				mcbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				mcbean.setEntries_Id(rs.getInt("Entries_Id"));
				mcbean.setCblog_Nick(rs.getString("Cblog_Nick"));
				mcbean.setCblog_Date(sdf.format(rs.getTimestamp("Cblog_Date")));
				mcbean.setCblog_Contents(rs.getString("Cblog_Contents"));
				result.add(mcbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyCommentBean[0]);
	}
	
	public MyWriteBean[] latestEntriesGet(int mypageid, int number){
		conn = connector.getConnection();
		ArrayList<MyWriteBean> result = new ArrayList<MyWriteBean>();
		
		try{
			
			int former = number - 1;
			if(former < 0){
				former = 0;
			}
			
			String sql = "select * from (select * from ENTRIES where Blogs_Id = ? ORDER BY Ent_DateString DESC) a where a.Ent_DateString limit ?, 3";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setInt(2, former);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				MyWriteBean mwbean = new MyWriteBean();
				mwbean.setID(rs.getInt("ID"));
				mwbean.setEnt_Title(rs.getString("Ent_Title"));
				mwbean.setEnt_Contents(rs.getString("Ent_Contents"));
				mwbean.setEnt_Nick(rs.getString("Ent_Nick"));
				mwbean.setEnt_Kategorie(rs.getString("Ent_Kategorie"));
				mwbean.setEnt_DateString(sdf.format(rs.getTimestamp("Ent_DateString")));
				mwbean.setEnt_Date(rs.getDate("Ent_Date"));
				mwbean.setEnt_ComCount(rs.getInt("Ent_ComCount"));
				result.add(mwbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyWriteBean[0]);
	}
	
	public MyCommentBean[] commentGet(int mypageid, int entriesid){
		conn = connector.getConnection();
		ArrayList<MyCommentBean> result = new ArrayList<MyCommentBean>();
		
		try{
			
			String sql = "select * from COMMENTBLOGS where Blogs_Id = ? && Entries_Id = ? ORDER BY Cblog_Date DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setInt(2, entriesid);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			
			while( rs.next()){
				MyCommentBean mcbean = new MyCommentBean();
				mcbean.setID(rs.getInt("ID"));
				mcbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				mcbean.setEntries_Id(rs.getInt("Entries_Id"));
				mcbean.setCblog_Nick(rs.getString("Cblog_Nick"));
				mcbean.setCblog_Date(sdf.format(rs.getTimestamp("Cblog_Date")));
				mcbean.setCblog_Contents(rs.getString("Cblog_Contents"));
				result.add(mcbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyCommentBean[0]);
	}
	
	public Integer[] membersIdGet(String id){
		conn = connector.getConnection();
		ArrayList<Integer> list = new ArrayList<Integer>();
		try{
			
			String sql = "select Members_Id from BLOGS where Blog_Share = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
					
				list.add(Integer.parseInt(rs.getString("Members_Id")));
					
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new Integer[0]);
	}
	
	public String[] membersNickGet(Integer[] id){
		conn = connector.getConnection();
		ArrayList<String> list = new ArrayList<String>();
		try{
			for(int i = 0; i<id.length;i++){
				String sql = "select * from MEMBERS where ID = ?";
			
				pstmt = conn.prepareStatement( sql );
			
				pstmt.setInt(1, id[i]);
			
				rs = pstmt.executeQuery();
			
				if( rs.next()){
					list.add(rs.getString("Mem_Nick"));					
				}
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new String[0]);
	}
	
	public void shareDelet(Integer id){
		conn = connector.getConnection();

		try{
				String sql = "UPDATE BLOGS SET `Blog_Share`=null WHERE `Members_Id`=? ";
			
				pstmt = conn.prepareStatement( sql );
			
				pstmt.setInt(1, id);
			
				pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public int entriesCount(int mypageid){
		conn = connector.getConnection();
		int commentcount = 0;
		try{

			String sql = "select count(*) from ENTRIES where Blogs_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				commentcount = rs.getInt("count(*)");		
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return commentcount;
	}
	
	public MyMainBean myPageDataGet(int mypageid){
		conn = connector.getConnection();
		MyMainBean mymainbean = new MyMainBean();
		try{

			String sql = "select * from BLOGS where ID = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				mymainbean.setMembers_Id(rs.getInt("Members_Id"));
				mymainbean.setBlog_Share(rs.getString("Blog_Share"));
				mymainbean.setBlog_Tilte(rs.getString("Blog_Title"));
				mymainbean.setBlog_Intro(rs.getString("Blog_Intro"));
				mymainbean.setBlog_Date(sdf.format(rs.getTimestamp("Blog_Date")));
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return mymainbean;
	}
	
	public void myPageRevision(int mypageid, MyMainBean mymainbean){
		conn = connector.getConnection();
		try{
	
			String sql = "UPDATE BLOGS SET `Blog_Share`=?, `Blog_Intro`=?, `Blog_Title`=? WHERE `ID`=?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, mymainbean.getBlog_Share());
			pstmt.setString(2, mymainbean.getBlog_Intro());
			pstmt.setString(3, mymainbean.getBlog_Tilte());
			pstmt.setInt(4, mypageid);
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public int productionCheck(int id){
		conn = connector.getConnection();
		int commentcount = 0;
		try{

			String sql = "select Bby_Check from BABYPLUS where Members_Id = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				commentcount = rs.getInt("Bby_Check");		
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return commentcount;
	}
	
	public int oldIdGet(int id){
		conn = connector.getConnection();
		int commentcount = 0;
		try{

			String sql = "select Map_Old from MAPPING where Map_Young = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				commentcount = rs.getInt("Map_Old");		
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return commentcount;
	}
	
	public String nick(int id){
		conn = connector.getConnection();
		try{

			String sql = "select Mem_Nick from MEMBERS where ID = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				String commentcount = rs.getString("Mem_Nick");		
				return commentcount;
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return null;
	}
	
}
